package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.reconsitution.CollectionUtils;
import com.iris.live.services.data.repositories.CarRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.ResultData;
import com.iris.live.services.services.CarService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.iris.live.services.common.ApprovalUtils;

import java.util.*;

/**
 * Created by sxy on 2016/8/1.
 */
@Repository
@Transactional//(readOnly = true)
public class CarRepositoryImpl extends BaseRepository implements CarRepository {

    @Override
    public List getCarDetail(CarService.GetCarDetailRequest request) {
        Map params = Maps.newHashMap();
        String sql = "select 车型编号, 车型详细 from LSH车型\n" +
                "where 退市日期 <> 'Old' and 来源 = :Source and 品牌 = :Brand and 级别 = :Level and 车型 = :Type";
        params.put("Source", request.source);
        params.put("Brand", request.brand);
        params.put("Level", request.level);
        params.put("Type", request.type);
        return this.getList(sql, params);
    }

    @Override
    public List getCarType(CarService.GetCarDetailRequest request) {
        Map params = Maps.newHashMap();
        String sql = "select 车型编号, 车型 from LSH车型 \n" +
                "where 退市日期 <> 'Old' and 来源 = :Source and 品牌 = :Brand and 级别 = :Level";
        params.put("Source", request.source);
        params.put("Brand", request.brand);
        params.put("Level", request.level);
        return this.getList(sql, params);
    }

    @Override
    public List getCarLevel(CarService.GetCarDetailRequest request) {
        Map params = Maps.newHashMap();
        String sql = "select 车型编号, 级别 from LSH车型\n" +
                "where 退市日期 <> 'Old' and 来源 = :Source and 品牌 = :Brand and 车型 = ''";
        params.put("Source", request.source);
        params.put("Brand", request.brand);
        return this.getList(sql, params);
    }

    @Override
    public List getCarBrand(CarService.GetCarDetailRequest request) {
        Map params = Maps.newHashMap();
        String sql = "select 车型编号, 品牌 from LSH车型\n" +
                "where 退市日期 <> 'Old' and 来源 = :Source and 车型 = '' and 级别 = ''";
        params.put("Source", request.source);
        return this.getList(sql, params);
    }

    @Override
    public List getAllCar(List<String> brands,String permit) {
            StringBuffer sql = new StringBuffer("select 车型编号,来源,品牌,级别,车型,车型详细 from LSH车型 ");
        if(ApprovalUtils.GROUP_PERMIT.equals(permit)){
            return this.getList(sql.toString());
        }
        if(brands==null||brands.size()==0) {
            return null;
        }
        sql.append(" where 品牌 in (''");
        for(String brand:brands){
            sql.append(",'");
            sql.append(brand);
            sql.append("'");
        }
        sql.append(")");
        return this.getList(sql.toString());
    }

    @Override
    public List getAllModel(List<String> brands,String permit) {
        StringBuffer sql = new StringBuffer("select 车型编号,来源,品牌,级别,车型,车型详细 from LSH车型 ");
        if(ApprovalUtils.GROUP_PERMIT.equals(permit)){
            return this.getList(sql.toString());
        }
        if(brands==null||brands.size()==0) {
            return null;
        }
        sql.append(" where (车型详细 IS NOT NULL AND  车型详细 <> '') AND 品牌 in (''");
        for(String brand:brands){
            sql.append(",'");
            sql.append(brand);
            sql.append("'");
        }
        sql.append(")");
        return this.getList(sql.toString());
    }

    @Override
    public int getCarNumber(CarService.GetCarNumberRequest request) {
        String sql = "select 车型编号 from LSH车型 where 采购价 is null \n";
        Map params = new HashMap();
        if (!Strings.isNullOrEmpty(request.source)){
            sql += " and 来源 = :source \n";
            params.put("source",request.source);
        }else{
            sql += " and 来源 is null \n";
        }
        if (!Strings.isNullOrEmpty(request.brand)){
            sql += " and 品牌 = :brand \n";
            params.put("brand",request.brand);
        }else{
            sql += " and 品牌 is null \n";
        }
        if (!Strings.isNullOrEmpty(request.level)){
            sql += " and 级别 = :level \n";
            params.put("level",request.level);
        }else{
            sql += " and 级别 is null \n";
        }
        if (!Strings.isNullOrEmpty(request.type)){
            sql += " and 车型 = :type \n";
            params.put("type",request.type);
        }else{
            sql += " and 车型 is null \n";
        }
        if (!Strings.isNullOrEmpty(request.detail)){
            sql += " and 车型详细 = :detail \n";
            params.put("detail",request.detail);
        }else{
            sql += " and 车型详细 is null \n";
        }
        List list = this.getList(sql,params);

        return list.size() > 0 ? (int)((Map)list.get(0)).get("车型编号"): 0;
    }

    @Override
    public List getLshStoreByProIdAndPermit(String proId, String permit) {
        String sql = "SELECT * FROM lsh库存表 WHERE 生产号 =:proId AND `所属经销商` =:permit";
        Map map = Maps.newHashMap();
        map.put("proId", proId);
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    // 3，获取当前车辆状态信息
    @Override
    public List getCurrCarStatus(String permit) {
        String sql="SELECT `生产号`,`车辆物流状态`,B.`销售顾问`,C.`顾客姓名`,C.`电话号码`,\n" +
                "  D.`订单申请日期`,L.`车型详细`,A.`颜色` FROM `lsh库存表`\n" +
                "   AS A  LEFT JOIN LSH车型 AS L ON A.`车型编号`=L.`车型编号`\n" +
                "    LEFT JOIN `报价单表` AS B ON A.`报价编号`=B.`报价编号`\n" +
                "   LEFT JOIN `顾客记录汇总` AS C ON B.`顾客编号`=C.顾客编号\n" +
                "   LEFT JOIN `报价订单表` AS D ON B.`订单编号`=D.订单编号 \n" +
                "   WHERE B.`订单编号` > 0 AND `所属经销商`=:permit AND `生产号` IS NOT NULL \n" +
                "    AND `生产号` NOT LIKE 'IRISTechnologies%' AND (IFNULL(`出库日期`,'') = '' OR `出库日期` < '1971-01-01')";
        Map map =Maps.newHashMap();
        map.put("permit",permit);
        return this.getList(sql,map);
    }

    public PageData getCarDetailBatchAlls(CarService.getCarDetailBatchAlls req, String permit, String postman) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (!Strings.isNullOrEmpty(req.brand)) {
            sql += " and a.品牌  like  '%" + req.brand + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.origin)) {
            sql += " and a.来源 like  '%" + req.origin + "%' ";
        }
   /*     carSeries;//车系  级别
        public String  carModel;//车型
        public String  carStyle;//车款 车型详细 */

        if (!Strings.isNullOrEmpty(req.carClass)) {
            sql += " and a.级别 like  '%" + req.carClass + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.carModel)) {
            sql += " and a.车型 like  '%" + req.carModel + "%' ";
        }
        if (!Strings.isNullOrEmpty(req.carStyle)) {
            sql += " and a.车型详细 like  '%" + req.carStyle + "%' ";
        }
        sql += "  and (a.车型详细 is not null and a.车型详细<> '' )   ORDER BY a.更新时间 DESC";
        Map map = Maps.newHashMap();

        return this.getPage(sql, map, req.getPageSize(), req.getPageIndex());
    }

    public List getCarDetailBatchAllsIsItem(CarService.getCarDetailBatchAlls req, String permit, String postman) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1 and a.Variant='" + req.Variant + "' ";
        sql += "  ORDER BY a.更新时间 DESC";
        Map map = Maps.newHashMap();

        return this.getList(sql, map);
    }


    public boolean getCarDetailBatchByVariant(String vars) {
        String sql = " SELECT * FROM `lsh车型` a where 1=1  ";
        boolean flag = false;
        if (!Strings.isNullOrEmpty(vars)) {
            sql += " and a.C_PKID=" + vars + " ";
        } else {
            return false;
        }
        List booelist = new ArrayList();
        booelist = this.getList(sql);
        if (booelist != null && booelist.size() > 0) {
            flag = true;
        } else {
            flag = false;
        }
        return flag;
    }


    public List getListByBrandAndOrigin(String brand, String origin) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand.trim() + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin.trim() + "' ";
        }
        sql += " and a.级别= ''  and a.车型= '' " +
                " and a.车型详细= '' ";

        sql += "  ORDER BY a.更新时间 DESC";
        return this.getList(sql);
    }


    public List getListByBrandAndOrigins(String brand, String origin) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin + "' ";
        }
   /*     carSeries;//车系  级别
        public String  carModel;//车型
        public String  carStyle;//车款 车型详细 */
        sql += " and    a.级别<>''   and a.车型= '' " +
                " and a.车型详细= '' ";

        sql += "  ORDER BY a.更新时间 DESC";
        return this.getList(sql);
    }

    public List getListByBrandAndOriginAndcarClass(String brand, String origin, String carclass) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand.trim() + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin.trim() + "' ";
        }
        if (carclass != null && !"".equals(carclass)) {
            sql += " and a.级别= '" + carclass.trim() + "' ";
        }
   /*     carSeries;//车系  级别
        public String  carModel;//车型
        public String  carStyle;//车款 车型详细 */
        sql += "  and a.车型='' and  a.车型详细= '' ";
        sql += "  ORDER BY a.更新时间 DESC";
        return this.getList(sql);
    }

    public List getListByBrandAndOriginAndcarClasss(String brand, String origin, String carclass) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin + "' ";
        }
        if (carclass != null && !"".equals(carclass)) {
            sql += " and a.级别= '" + carclass + "'  ";
        }
   /*     carSeries;//车系  级别
        public String  carModel;//车型
        public String  carStyle;//车款 车型详细 */
        sql += "  and  a.车型<>''   and  a.车型详细= '' ";

        return this.getList(sql);
    }


    public List getListByBrandAndOriginCarclassAndCarModel(String brand, String origin, String carclass, String carmodel) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin + "' ";
        }
        if (carclass != null && !"".equals(carclass)) {
            sql += " and a.级别= '" + carclass + "' ";
        }
        if (carmodel != null && !"".equals(carmodel)) {
            sql += " and a.车型= '" + carmodel + "' ";
        }
   /*     carSeries;//车系  级别
        public String  carModel;//车型
        public String  carStyle;//车款 车型详细 */
        sql += " and a.车型详细= '' ";
        sql += "  ORDER BY a.更新时间 DESC";
        return this.getList(sql);
    }

    public List getListByBrandAndOriginCarclassAndCarModels(String brand, String origin, String carclass, String carmodel) {
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (brand != null && !"".equals(brand)) {
            sql += " and a.品牌= '" + brand + "' ";
        }
        if (origin != null && !"".equals(origin)) {
            sql += " and a.来源= '" + origin + "' ";
        }
        if (carclass != null && !"".equals(carclass)) {
            sql += " and a.级别= '" + carclass + "' ";
        }
        if (carmodel != null && !"".equals(carmodel)) {
            sql += " and a.车型= '" + carmodel + "' ";
        }
        sql += " and     a.车型详细<>'' ";

        return this.getList(sql);
    }


    public boolean IsUpdateCarType(CarService.getCarDetailBatchAlls item) {
        boolean flag = false;
        String sql = "SELECT * FROM `lsh车型` a where 1=1  ";
        if (item.brand != null && !"".equals(item.brand)) {
            sql += " and a.品牌= '" + item.brand + "' ";
        }
        if (item.origin != null && !"".equals(item.origin)) {
            sql += " and a.来源= '" + item.origin + "' ";
        }
        if (item.carClass != null && !"".equals(item.carClass)) {
            sql += " and a.级别= '" + item.carClass + "' ";
        }
        if (item.carModel != null && !"".equals(item.carModel)) {
            sql += " and a.车型= '" + item.carModel + "' ";
        }
        if (item.carStyle != null && !"".equals(item.carStyle)) {
            sql += " and a.车型详细= '" + item.carStyle + "' ";
        }  //5


        if (item.Variant != null && !"".equals(item.Variant)) {
            sql += " and a.Variant= '" + item.Variant + "' ";
        } else {
            sql += " and (a.Variant is null or a.Variant =''  )";
        }
        if (item.Type != null && !"".equals(item.Type)) {
            sql += " and a.type= '" + item.Type + "' ";
        } else {
            sql += " and (a.type is null or a.type =''  )";
        }//7


        List islist = this.getList(sql);
        if (islist != null && islist.size() > 0) {
            flag = true;
        }
        return flag;
    }

    public List IsUpdateCarTypeList(CarService.getCarDetailBatchAlls item) {

        String sql = "SELECT 车型编号 as id FROM `lsh车型` a where 1=1  ";
        if (item.brand != null && !"".equals(item.brand)) {
            sql += " and a.品牌= '" + item.brand + "' ";
        }
        if (item.origin != null && !"".equals(item.origin)) {
            sql += " and a.来源= '" + item.origin + "' ";
        }
        if (item.carClass != null && !"".equals(item.carClass)) {
            sql += " and a.级别= '" + item.carClass + "' ";
        }
        if (item.carModel != null && !"".equals(item.carModel)) {
            sql += " and a.车型= '" + item.carModel + "' ";
        }
        if (item.carStyle != null && !"".equals(item.carStyle)) {
            sql += " and a.车型详细= '" + item.carStyle + "' ";
        }  //5


        if (item.Variant != null && !"".equals(item.Variant)) {
            sql += " and a.Variant= '" + item.Variant + "' ";
        } else {
            sql += " and (a.Variant is null or a.Variant =''  )";
        }
        if (item.Type != null && !"".equals(item.Type)) {
            sql += " and a.type= '" + item.Type + "' ";
        } else {
            sql += " and (a.type is null or a.type =''  )";
        }//7


        List islist = this.getList(sql);

        return islist;
    }
    public  PageData getReturnCarList(CarService.ReturnCarParam req,String permit){
        String exesql =ReturnCarSql(req);
        Map params = new HashMap();
        params.put("dealer",permit);
        return this.getPage(exesql, params, req.getPageSize(), req.getPageIndex());

    }
    private static  String ReturnCarSql(CarService.ReturnCarParam req){//应收金额，采购日期，备注 批发日期 改为 采购日期
        String  exesql="";
            if(req.state==null||req.state.equals("1")){//查询已退（大昌行只查询已退）
                StringBuffer tmpsql = new StringBuffer();

                tmpsql.append("select kc.`库存编号`,cx.品牌, cx.来源,cx.`车型`,cx.车型详细 车款,kc.`生产号`,if(kc.车架号 like 'IRISTech%','',kc.车架号) as 车架号, kc.`批发日期` as 采购日期,'' as 备注,kc.实际MSRP含税 AS 应收金额, ")
                        .append("  IF(  cx.来源 = 'CBU',  cx.级别,  CONCAT(cx.来源, ' ', cx.级别)   ) AS 车系 , DATE_FORMAT(kc.`del_date`, '%Y-%m-%d')  AS 退车日期  ")
                        .append(" FROM  `lsh库存表_log` kc   LEFT JOIN `lsh车型` cx  ON cx.车型编号 = kc.`车型编号`  ")
                        .append("  LEFT JOIN (SELECT MAX(id) AS id,stock_code,logistics_status , financial_status, in_dealer, out_dealer FROM car_transfer_log GROUP BY stock_code   ORDER BY id DESC ) AS  car ")
                        .append("  on car.stock_code=kc.库存编号 ")
                        .append("  LEFT JOIN pushApprovalWorkFlow pu ON CONVERT(car.id,CHAR)=pu.`quotedPriceCodeOrVIN` AND pu.`type` IN (8,9) AND pu.approvalStatus=2 ")
                        .append(" left join ")
                        .append(" (" )
                        .append(" select a.* FROM ")
                        .append(" ( ")
                        .append(" SELECT a.* , b.`订单状态` 订单状态 ")
                        .append(" FROM 报价单表 a , 报价订单表  b ")
                        .append(" where A.许可=:dealer and a.订单编号=b.`订单编号` ")
                        .append(" and 是否驳回 = '否'  order by  `报价单号` desc ")
                        .append(" ) a ")
                        .append(" group by a.初始报价单号 ")
                        .append(" ) 报价单表 ")
                        .append(" ON `报价单表`.`库存编号`=  kc.库存编号 and 报价单表.订单状态 ")
                        .append(" in( '意向订单O','意向订单C', '订单修改' , '订单C' , '订单O' , '合同O' , '合同C' , '开票O' , '开票C' , '换票C' , '换票O' , '退票O' , '合同修改' , '换票','退订','退订R' ) ")
                        .append(" LEFT JOIN `报价付款详细` ON `报价单表`.`报价编号` = `报价付款详细`.`报价编号` AND `付款事由` = '总计'")
                         .append(" where 1=1 ");

                if(StringUtils.isNotBlank(req.startWholesaleDate)){
                    tmpsql.append(" and kc.`批发日期`>='"+req.startWholesaleDate+"'");
                }
                if(StringUtils.isNotBlank(req.endWholesaleDate)) {
                    tmpsql.append(" and kc.`批发日期`<='" +req.endWholesaleDate + "'");
                }
               /* if (req.state==null||req.state.equals("0")) {
                    tmpsql.append(" and  kc.`批发日期` IS NOT NULL AND kc.is_delete <> '1'  AND  kc.所属经销商=kc.采购批发商 ");
                }*/
                if(StringUtils.isNotBlank(req.dealer)){
                    tmpsql.append(" and  kc.所属经销商='"+req.dealer+"'");
                }
                if(StringUtils.isNotBlank(req.productionNo)){

                    tmpsql.append(" and kc.`生产号` like '%"+req.productionNo +"%'" );

                }
                if(StringUtils.isNotBlank(req.frameNo)){
                    tmpsql.append(" and kc.`车架号` like '%"+req.frameNo +"%'" );
                    tmpsql.append("  and kc.`车架号` not like 'IRISTech%'");

                }
                tmpsql.append("  and kc.`车架号` not like 'IRISTechnologies%'");

                tmpsql.append("  and ((car.logistics_status = '调入入库' and car.financial_status = '调车款项到账') or isnull(car.stock_code)) ");
                tmpsql.append("  ORDER BY kc.`批发日期` DESC ");
                exesql=tmpsql.toString();
            }
            else{
                throw  new RuntimeException("参数req.state不正确");

            }

        return exesql;
    }

    public int updateReturnCar(String stockNo,String operator) {

        String sql = "UPDATE  lsh库存表 SET \n" +
                "车型编号=NULL,生产日期=NULL,欧版底盘号=NULL,发动机号=NULL,VSB号=NULL,颜色代码=NULL, 颜色=NULL, 内饰代码=NULL, 内饰=NULL, 原厂配置=NULL,加配配置=NULL, 加配价格含税=NULL, 采购价格含税=NULL, 采购价格未税=NULL,BI车款标配MSRP含税=NULL,标配MSRP含税=NULL,实际MSRP含税=NULL, \n" +
                " SNP含税=NULL,厂家确认订单日期=NULL,预计到港日期=NULL,实际到港日期=NULL,预计到店日期=NULL,预计下线日期=NULL,港口代码=NULL,  港口名称=NULL,\n" +
                "\t\t  车辆物流状态 =NULL,\n" +
                "\t\t  车辆零售状态=NULL,\n" +
                "\t\t  车辆零售类型 =NULL,\n" +
                "\t\t  HOLD日期=NULL,\n" +
                "\t\t  申请释放日期=NULL,\n" +
                "\t\t  批发日期 =NULL,\n" +
                "\t\t  批发付款到期日 =NULL,\n" +
                "\t\t  NBI车辆状态 =NULL,\n" +
                "\t\t  店内车辆位置 =NULL,\n" +
                "\t\t  二次PDI完成日期 =NULL,\n" +
                "\t\t  二次PDI结果 =NULL,\n" +
                "\t\t  入库日期 =NULL,\n" +
                "\t\t  出库日期 =NULL,\n" +
                "\t\t  库龄天数=NULL,\n" +
                "\t\t  PDI间隔天数 =NULL,\n" +
                "\t\t  建议三次PDI日期=NULL,\n" +
                "\t\t  三次PDI完成日期 =NULL,\n" +
                "\t\t  三次PDI结果 =NULL,\n" +
                "\t\t  车辆受损情况 =NULL,\n" +
                "\t\t  商检单到店状态 =NULL,\n" +
                "\t\t  关单到店状态 =NULL,\n" +
                "\t\t  合格证到店状态 =NULL,\n" +
                "\t\t  批发商 \t=NULL,\n" +
                "\t\t  采购批发商 =NULL,\n" +
                "\t\t  所属经销商 =NULL,\n" +
                "\t\t  外部可见级别=NULL,\n" +
                "\t\t  内部可见级别=NULL,\n" +
                "\t\t  调车物流状态=NULL,\n" +
                "\t\t  调车财务状态=NULL,\n" +
                "\t\t  调入经销商 =NULL,\n" +
                "\t\t  调出经销商 =NULL,\n" +
                "\t\t  调入内部申请日 =NULL,\n" +
                "\t\t  调入外部申请日 =NULL,\n" +
                "\t\t  调出确认日期 =NULL,\n" +
                "\t\t  协商调车款项金额 =NULL,\n" +
                "\t\t  调车发票开出日期 =NULL,\n" +
                "\t\t  调车款项到帐日期 =NULL,\n" +
                "\t\t  调车款项到帐金额 =NULL,\n" +
                "\t\t  报价编号   =NULL,\n" +
                "\t\t  上报CESAR日期 =NULL,\n" +
                "\t\t  长库龄推送=NULL,\n" +
                "\t\t  特价 =NULL,\n" +
                "\t\t  特价有效期=NULL,\n" +
                "\t\t  长库龄备注=NULL,\n" +
                "\t\t  合格证号 =NULL,\n" +
                "\t\t  商检单号 =NULL,\n" +
                "\t\t  关单号 =NULL,\n" +
                "\t\t  申请销售顾问 =NULL,\n" +
                "\t\t  调车理由=NULL,\n" +
                "\t\t  机动车种类 =NULL,\n" +
                "\t\t  核载人数= NULL,\n" +
                "\t\t  核载质量= NULL,\n" +
                "\t\t  排量 =NULL,\n" +
                "\t\t  功率 =NULL,\n" +
                "\t\t  VSB财务状态 =NULL,\n" +
                "\t\t  car_state=00,is_delete=1,VSB财务状态=null,所属经销商=null,批发日期=null, delete_user='"+operator+"'  WHERE 库存编号 ='"+stockNo+"'";
        Map map =new HashMap();

        return  this.executeUpdate(sql,map);





       /* Lsh库存表Model sel库存表Model= lsh库存tableRepository.getOne(Integer.parseInt(stockNo));

        Lsh库存表Model lsh库存表Model=new Lsh库存表Model();
        lsh库存表Model.set库存编号(sel库存表Model.get库存编号());
        lsh库存表Model.set车型编号(sel库存表Model.get车型编号());
        lsh库存表Model.set生产号(sel库存表Model.get生产号());
        lsh库存表Model.set车架号(sel库存表Model.get车架号());
        lsh库存表Model.setIsDelete((byte) 1);
        lsh库存表Model.setDeleteUser(operator);
        lsh库存tableRepository.save(lsh库存表Model);
        return  1;*/

    }
    public int update库存表log(String stockNos,String operator) {

        String sql = "UPDATE  lsh库存表_log  SET operator='"+operator+"'  WHERE 库存编号  IN ("+stockNos+") ";
        Map map =new HashMap();

        return  this.executeUpdate(sql,map);
    }

    public  int insertReturn(String stockNos){

        StringBuilder stringBuilder=new StringBuilder();
        stringBuilder.append("INSERT INTO lsh库存表_log (\n" +
                "  库存编号, 车型编号, 生产日期, 生产号, 车架号,欧版底盘号, 发动机号, VSB号, 颜色代码, 颜色,\n" +
                "  内饰代码, 内饰,原厂配置,加配配置,加配价格含税,采购价格含税,采购价格未税, BI车款标配MSRP含税,\n" +
                "  标配MSRP含税,实际MSRP含税,SNP含税,厂家确认订单日期,预计到港日期,实际到港日期,预计到店日期,\n" +
                "  预计下线日期,港口代码,港口名称,车辆物流状态,车辆零售状态,车辆零售类型,\n" +
                "  HOLD日期,申请释放日期,批发日期,批发付款到期日,NBI车辆状态,店内车辆位置,\n" +
                "  二次PDI完成日期,二次PDI结果,入库日期,出库日期,库龄天数,PDI间隔天数,建议三次PDI日期,三次PDI完成日期,三次PDI结果,\n" +
                "  车辆受损情况,商检单到店状态,关单到店状态,合格证到店状态,批发商,采购批发商,所属经销商,外部可见级别,内部可见级别,\n" +
                "  调车物流状态,调车财务状态,调入经销商,调出经销商,调入内部申请日,调入外部申请日,调出确认日期,协商调车款项金额,\n" +
                "  调车发票开出日期,调车款项到帐日期,调车款项到帐金额,报价编号,上报CESAR日期,长库龄推送,特价,特价有效期,\n" +
                "  长库龄备注,合格证号,商检单号,关单号,申请销售顾问,调车理由,机动车种类,核载人数,核载质量,排量,功率,\n" +
                "  VSB财务状态,car_state,is_delete,delete_user,create_time,update_time \n" +
                "  \n" +
                ") \n" +
                "SELECT \n" +
                "   库存编号, 车型编号, 生产日期, 生产号, 车架号,欧版底盘号, 发动机号, VSB号, 颜色代码, 颜色,\n" +
                "  内饰代码, 内饰,原厂配置,加配配置,加配价格含税,采购价格含税,采购价格未税, BI车款标配MSRP含税,\n" +
                "  标配MSRP含税,实际MSRP含税,SNP含税,厂家确认订单日期,预计到港日期,实际到港日期,预计到店日期,\n" +
                "  预计下线日期,港口代码,港口名称,车辆物流状态,车辆零售状态,车辆零售类型,\n" +
                "  HOLD日期,申请释放日期,批发日期,批发付款到期日,NBI车辆状态,店内车辆位置,\n" +
                "  二次PDI完成日期,二次PDI结果,入库日期,出库日期,库龄天数,PDI间隔天数,建议三次PDI日期,三次PDI完成日期,三次PDI结果,\n" +
                "  车辆受损情况,商检单到店状态,关单到店状态,合格证到店状态,批发商,采购批发商,所属经销商,外部可见级别,内部可见级别,\n" +
                "  调车物流状态,调车财务状态,调入经销商,调出经销商,调入内部申请日,调入外部申请日,调出确认日期,协商调车款项金额,\n" +
                "  调车发票开出日期,调车款项到帐日期,调车款项到帐金额,报价编号,上报CESAR日期,长库龄推送,特价,特价有效期,\n" +
                "  长库龄备注,合格证号,商检单号,关单号,申请销售顾问,调车理由,机动车种类,核载人数,核载质量,排量,功率,\n" +
                "  VSB财务状态,car_state,is_delete,delete_user,create_time,update_time\n" +
                "FROM\n" +
                "  lsh库存表 \n" +
                "WHERE 库存编号 IN ("+stockNos+")");

        return this.executeUpdate(stringBuilder.toString(),null);
    }

    @Override
    public List<Map<String,Object>> getQuotationByStockNumber(String stockNumber) {
        String sql = "select b.*,c.订单号,d.电话号码,d.顾客姓名\n" +
                "from (select * from 报价单表 a where a.是否还原点 = '是' and a.是否驳回 = '否' and a.库存编号 = :stockNumber order by a.报价编号 desc) b \n" +
                "join 报价订单表 c on b.订单编号 = c.订单编号\n" +
                "join 顾客记录汇总 d on b.顾客编号 = d.顾客编号\n" +
                "where c.订单失销日期 is NULL\n" +
                "and c.订单审批通过日期 is not null\n" +
                "group by b.初始报价单号 ";
        Map params = new HashMap();
        params.put("stockNumber",stockNumber);
        return this.getList(sql,params);
    }

    //获得所有的车辆物流状态
    public ResultData getLogisticsStatus(){
        String sql=" SELECT DISTINCT(车辆物流状态) FROM lsh库存表 WHERE (车辆物流状态 IS NOT NULL AND 车辆物流状态<>'') ";
        List list=this.getList(sql);
        return new ResultData("true","查询成功",new PageData(list,list.size()));
    }

    @Override
    public List<String> getAvailableVehicleBrands(String userName) {
        List<String> brands = new ArrayList<>();
        String sql = "SELECT distinct a.品牌 from `lsh车型` a where (a.级别 is null or a.级别='') and (a.品牌 is not null and a.品牌 <> '')" ;//order by display_index asc"
        List<Map> list = super.getList(sql);
        if(! CollectionUtils.isEmpty(list)){
            for(Map map : list){
                brands.add(map.get("品牌").toString());
            }
        }

        return brands;
    }

    /*@Override
    @Transactional(readOnly = true)
    public List<String> getAvailableVehicleBrands(String userName) {
        String sql = "SELECT 品牌 FROM 用户  WHERE 用户名 = :userName";
        Map<String, String> params = new HashMap<>();
        params.put("userName", userName);
        List<Map> list = super.getList(sql, params);
        if(CollectionUtils.isEmpty(list)){
            return null;
        }else{
            String brand = (list.get(0)).get("品牌") == null ? "" : (list.get(0)).get("品牌").toString();
            if(brand.endsWith(";")){
                brand = brand.substring(0, brand.lastIndexOf(";"));
            }

            if(brand.startsWith(";")){
                brand = brand.substring(1);
            }
            return Arrays.asList(brand.split(";"));
        }
    }*/
}
